<?php

namespace Icsoc\ReportBundle\Model;

use Symfony\Component\DependencyInjection\ContainerInterface;

/**
 * Class ChartsModel
 * @package Icsoc\ReportBundle\Model
 */
class ChartsModel
{

    /**
     * @var \Doctrine\DBAL\Connection
     */
    private $dbal;
    /**
     * @var \Doctrine\DBAL\Connection|string
     */
    private $cdrDbal;

    /**
     * ChartsModel constructor.
     * @param ContainerInterface $container
     */
    public function __construct(ContainerInterface $container)
    {
        $this->container = $container;
        $this->cdrDbal = $this->container->get('doctrine.dbal.cdr_connection');
        $this->dbal = $this->container->get('doctrine.dbal.default_connection');
    }

    /**
     * 坐席通话量按坐席图表数据
     *
     * @param array $param
     * @return array
     */
    public function getAgentCallChartsByAgentData(array $param = array())
    {
        if (empty($param)) {
            return array(
                'code' => 401,
                'message' => '统计参数为空'
            );
        }

        if (!is_array($param)) {
            return array(
                'code' => 402,
                'message' => '统计参数格式不为数组'
            );
        }

        if (empty($param['vcc_id'])) {
            return array(
                'code' => 403,
                'message' => '未定义的企业'
            );
        }

        if (empty($param['agents'])) {
            return array(
                'code' => 405,
                'message' => '未选择统计范围'
            );
        }

        $condition = ' vcc_id = ' . $param['vcc_id'] . ' AND result = 0';

        /** @var  $authority (获取数据权限)*/
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 409,
                'message' => '权限内无任何数据'
            );
        } else {
            $condition .= $authority;
        }

        if (!empty($param['agents'])) {
            $condition .= ' AND ag_id IN ('.$param['agents'].')';
        }

        if (!empty($param['ques'])) {
            $condition .= ' AND que_id IN ('.$param['ques'].')';
        }

        switch($param['time_stamp']){
            case '0':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND start_date = "' . $param['start_date'] . '"';
                }
                break;
            case '1':
                if (!empty($param['start_date'])) {
                    $startDtae = explode('-', $param['start_date']);
                    if (!is_array($startDtae) || count($startDtae) != 2) {
                        return array(
                            'code' => 405,
                            'message' => '日期格式错误'
                        );
                    }
                    $condition .= ' AND start_year = "' . $startDtae[0] . '" AND start_month = "' . $startDtae[1] . '"';
                }
                break;
            case '2':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND start_date >= "' . $param['start_date'] . '"';
                }
                if (!empty($param['end_date'])) {
                    $condition .= ' AND start_date <= "' . $param['end_date'] . '"';
                }
                break;
        }

        $result = $this->cdrDbal->fetchAll(
            'SELECT SUM(CASE WHEN call_type IN (1,3,5) THEN 1 ELSE 0 END) AS out_num,' .
            'SUM(CASE WHEN call_type IN (2,4,6) THEN 1 ELSE 0 END) AS in_num,COUNT(*) AS total_num,' .
            'SUM(conn_secs) AS total_secs,AVG(conn_secs) AS avg_secs,ag_id,ag_num,ag_name ' .
            'FROM win_agcdr WHERE ' . $condition .
            ' GROUP BY ag_id'
        );

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => count($result),
            'data' => $result
        );
    }

    /**
     * 坐席通话量按日期图表数据
     *
     * @param $param
     * @return array
     */
    public function getAgentCallChartsByDateData($param)
    {
        if (empty($param)) {
            return array(
                'code' => 401,
                'message' => '统计参数为空'
            );
        }

        if (!is_array($param)) {
            return array(
                'code' => 402,
                'message' => '统计参数格式不为数组'
            );
        }

        if (empty($param['vcc_id'])) {
            return array(
                'code' => 403,
                'message' => '未定义的企业'
            );
        }

        if (empty($param['agents'])) {
            return array(
                'code' => 405,
                'message' => '未选择统计范围'
            );
        }

        $condition = ' vcc_id = ' . $param['vcc_id'] . ' AND result = 0';

        /** @var  $authority (获取数据权限)*/
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 409,
                'message' => '权限内无任何数据'
            );
        } else {
            $condition .= $authority;
        }

        if (!empty($param['agents'])) {
            $condition .= ' AND ag_id IN ('.$param['agents'].')';
        }

        if (!empty($param['ques'])) {
            $condition .= ' AND que_id IN ('.$param['ques'].')';
        }

        switch($param['time_stamp']){
            case '0':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND start_date = "' . $param['start_date'] . '"';
                }
                break;
            case '1':
                if (!empty($param['start_date'])) {
                    $startDtae = explode('-', $param['start_date']);
                    if (!is_array($startDtae) || count($startDtae) != 2) {
                        return array(
                            'code' => 405,
                            'message' => '日期格式错误'
                        );
                    }
                    $condition .= ' AND start_year = "' . $startDtae[0] . '" AND start_month = "' . $startDtae[1] . '"';
                }
                break;
            case '2':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND start_date >= "' . $param['start_date'] . '"';
                }
                if (!empty($param['end_date'])) {
                    $condition .= ' AND start_date <= "' . $param['end_date'] . '"';
                }
                break;
        }

        $result = $this->cdrDbal->fetchAll(
            'SELECT SUM(CASE WHEN call_type IN (1,3,5) THEN 1 ELSE 0 END) AS out_num,' .
            'SUM(CASE WHEN call_type IN (2,4,6) THEN 1 ELSE 0 END) AS in_num,COUNT(*) AS total_num,' .
            'SUM(conn_secs) AS total_secs,AVG(conn_secs) AS avg_secs,ag_id,ag_num,ag_name,start_date ' .
            'FROM win_agcdr WHERE ' . $condition .
            ' GROUP BY start_date'
        );

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => count($result),
            'data' => $result
        );
    }

    /**
     * 坐席工作量按坐席图表数据
     *
     * @param $param
     * @return array
     */
    public function getAgentWorkChartsByAgentData($param)
    {
        if (empty($param)) {
            return array(
                'code' => 401,
                'message' => '统计参数为空'
            );
        }

        if (!is_array($param)) {
            return array(
                'code' => 402,
                'message' => '统计参数格式不为数组'
            );
        }

        if (empty($param['vcc_id'])) {
            return array(
                'code' => 403,
                'message' => '未定义的企业'
            );
        }

        if (empty($param['agents'])) {
            return array(
                'code' => 405,
                'message' => '未选择统计范围'
            );
        }

        $condition = ' vcc_id = ' . $param['vcc_id'];

        /** @var  $authority (获取数据权限)*/
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 2);
        if ($authority === false) {
            return array(
                'code' => 409,
                'message' => '权限内无任何数据'
            );
        } else {
            $condition .= $authority;
        }

        if (!empty($param['agents'])) {
            $condition .= ' AND ag_id IN ('.$param['agents'].')';
        }

        switch($param['time_stamp']){
            case '0':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND nowdate = "' . $param['start_date'] . '"';
                }
                break;
            case '1':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND start_date = "' . $param['start_date'] . '"';
                }
                break;
            case '2':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND nowdate >= "' . $param['start_date'] . '"';
                }
                if (!empty($param['end_date'])) {
                    $condition .= ' AND nowdate <= "' . $param['end_date'] . '"';
                }
                break;
        }

        $result = $this->cdrDbal->fetchAll(
            "SELECT SUM(login_secs) AS total_login,SUM(conn_secs) AS total_conn,SUM(busy_secs) AS total_busy," .
            "SUM(ready_secs) AS total_ready,SUM(wait_secs) AS total_wait,SUM(in_num) AS total_in," .
            "SUM(out_num) AS total_out,ag_id,ag_name,ag_num " .
            "FROM rep_agent_day " .
            "WHERE " . $condition .
            " GROUP BY ag_id"
        );

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => count($result),
            'data' => $result
        );
    }
    /**
     * 坐席工作量按日期图表数据
     *
     * @param $param
     * @return array
     */
    public function getAgentWorkChartsByDateData($param)
    {
        if (empty($param)) {
            return array(
                'code' => 401,
                'message' => '统计参数为空'
            );
        }

        if (!is_array($param)) {
            return array(
                'code' => 402,
                'message' => '统计参数格式不为数组'
            );
        }

        if (empty($param['vcc_id'])) {
            return array(
                'code' => 403,
                'message' => '未定义的企业'
            );
        }

        if (empty($param['agents'])) {
            return array(
                'code' => 405,
                'message' => '未选择统计范围'
            );
        }

        $condition = ' vcc_id = ' . $param['vcc_id'];

        /** @var  $authority (获取数据权限)*/
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 2);
        if ($authority === false) {
            return array(
                'code' => 409,
                'message' => '权限内无任何数据'
            );
        } else {
            $condition .= $authority;
        }

        if (!empty($param['agents'])) {
            $condition .= ' AND ag_id IN ('.$param['agents'].')';
        }

        switch($param['time_stamp']){
            case '0':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND nowdate = "' . $param['start_date'] . '"';
                }
                break;
            case '1':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND start_date = "' . $param['start_date'] . '"';
                }
                break;
            case '2':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND nowdate >= "' . $param['start_date'] . '"';
                }
                if (!empty($param['end_date'])) {
                    $condition .= ' AND nowdate <= "' . $param['end_date'] . '"';
                }
                break;
        }

        $result = $this->cdrDbal->fetchAll(
            "SELECT SUM(login_secs) AS total_login,SUM(conn_secs) AS total_conn,SUM(busy_secs) AS total_busy," .
            "SUM(ready_secs) AS total_ready,SUM(wait_secs) AS total_wait,SUM(in_num) AS total_in," .
            "SUM(out_num) AS total_out,ag_id,ag_name,ag_num,start_date,nowdate " .
            "FROM rep_agent_day " .
            "WHERE " . $condition .
            " GROUP BY nowdate"
        );

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => count($result),
            'data' => $result
        );
    }

    /**
     * 技能组通话量图表数据按技能组
     *
     * @param $param
     * @return array
     */
    public function getQueueCallChartsByQueueData($param)
    {
        if (empty($param)) {
            return array(
                'code' => 401,
                'message' => '统计参数为空'
            );
        }

        if (!is_array($param)) {
            return array(
                'code' => 402,
                'message' => '统计参数格式不为数组'
            );
        }

        if (empty($param['vcc_id'])) {
            return array(
                'code' => 403,
                'message' => '未定义的企业'
            );
        }

        if (empty($param['node'])) {
            return array(
                'code' => 405,
                'message' => '未选择统计范围'
            );
        }

        $condition = ' vcc_id = ' . $param['vcc_id'];

        /** @var  $authority (获取数据权限)*/
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 409,
                'message' => '权限内无任何数据'
            );
        } else {
            $condition .= $authority;
        }

        if (!empty($param['node'])) {
            $condition .= ' AND queue_id IN ('.$param['node'].')';
        }

        switch($param['time_stamp']){
            case '0':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND nowdate = "' . $param['start_date'] . '"';
                }
                break;
            case '1':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND start_date = "' . $param['start_date'] . '"';
                }
                break;
            case '2':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND nowdate >= "' . $param['start_date'] . '"';
                }
                if (!empty($param['end_date'])) {
                    $condition .= ' AND nowdate <= "' . $param['end_date'] . '"';
                }
                break;
        }

        $result = $this->cdrDbal->fetchAll(
            "SELECT SUM(in_num) AS total_innum,SUM(lost_num) AS total_lost,SUM(conn_num) AS total_conn," .
            "SUM(queue_secs) AS total_quesecs,SUM(conn_secs) AS total_connsecs,SUM(wait_secs) AS total_waitsecs," .
            "SUM(deal_secs) AS total_dealsecs,queue_id,queue_name " .
            "FROM rep_queue_day WHERE " . $condition .
            " GROUP BY queue_id"
        );

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => count($result),
            'data' => $result
        );
    }

    /**
     * 技能组通话量图表数据按日期
     *
     * @param $param
     * @return array
     */
    public function getQueueCallChartsByDateData($param)
    {
        if (empty($param)) {
            return array(
                'code' => 401,
                'message' => '统计参数为空'
            );
        }

        if (!is_array($param)) {
            return array(
                'code' => 402,
                'message' => '统计参数格式不为数组'
            );
        }

        if (empty($param['vcc_id'])) {
            return array(
                'code' => 403,
                'message' => '未定义的企业'
            );
        }

        if (empty($param['node'])) {
            return array(
                'code' => 405,
                'message' => '未选择统计范围'
            );
        }

        $condition = ' vcc_id = ' . $param['vcc_id'];

        /** @var  $authority (获取数据权限)*/
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 409,
                'message' => '权限内无任何数据'
            );
        } else {
            $condition .= $authority;
        }

        if (!empty($param['node'])) {
            $condition .= ' AND queue_id IN ('.$param['node'].')';
        }

        switch($param['time_stamp']){
            case '0':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND nowdate = "' . $param['start_date'] . '"';
                }
                break;
            case '1':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND start_date = "' . $param['start_date'] . '"';
                }
                break;
            case '2':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND nowdate >= "' . $param['start_date'] . '"';
                }
                if (!empty($param['end_date'])) {
                    $condition .= ' AND nowdate <= "' . $param['end_date'] . '"';
                }
                break;
        }

        $result = $this->cdrDbal->fetchAll(
            "SELECT SUM(in_num) AS total_innum,SUM(lost_num) AS total_lost,SUM(conn_num) AS total_conn," .
            "SUM(queue_secs) AS total_quesecs,SUM(conn_secs) AS total_connsecs,SUM(wait_secs) AS total_waitsecs," .
            "SUM(deal_secs) AS total_dealsecs,start_date,nowdate " .
            "FROM rep_queue_day WHERE " . $condition .
            " GROUP BY nowdate"
        );

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => count($result),
            'data' => $result
        );
    }

    /**
     * 呼入地区分析图表数据
     *
     * @param $param
     * @return array
     */
    public function getInareaChartsData($param)
    {
        if (empty($param)) {
            return array(
                'code' => 401,
                'message' => '统计参数为空'
            );
        }

        if (!is_array($param)) {
            return array(
                'code' => 402,
                'message' => '统计参数格式不为数组'
            );
        }

        if (empty($param['vcc_id'])) {
            return array(
                'code' => 403,
                'message' => '未定义的企业'
            );
        }

        if (empty($param['node'])) {
            return array(
                'code' => 405,
                'message' => '未选择统计范围'
            );
        }

        $condition = ' vcc_id = ' . $param['vcc_id'];

        /** @var  $authority (获取数据权限)*/
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 409,
                'message' => '权限内无任何数据'
            );
        } else {
            $condition .= $authority;
        }

        switch($param['type']){
            case 'queue':
                $condition .= ' AND que_id IN (' .$param['node']. ')';
                break;
            case 'number':
                $condition .= ' AND server_num IN (' .$param['node']. ')';
                break;
        }

        switch($param['time_stamp']){
            case '0':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND start_date = "' . $param['start_date'] . '"';
                }
                break;
            case '1':
                if (!empty($param['start_date'])) {
                    $startDtae = explode('-', $param['start_date']);
                    if (!is_array($startDtae) || count($startDtae) != 2) {
                        return array(
                            'code' => 405,
                            'message' => '日期格式错误'
                        );
                    }
                    $condition .= ' AND start_year = "' . $startDtae[0] . '" AND start_month = "' . $startDtae[1] . '"';
                }
                break;
            case '2':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND start_date >= "' . $param['start_date'] . '"';
                }
                if (!empty($param['end_date'])) {
                    $condition .= ' AND start_date <= "' . $param['end_date'] . '"';
                }
                break;
        }

        $result = $this->cdrDbal->fetchAll(
            "SELECT province_name,SUM(nums) AS total " .
            "FROM rep_incdr_area WHERE " . $condition .
            " GROUP BY province_name ORDER BY area_code DESC"
        );

        return array(
            'code' => 200,
            'message' => 'ok',
            'data' => $result
        );
    }

    public function getInareaChartsDataFromMongo($param)
    {
        $startDate = $param['start_date'];
        $timeStamp = $param['time_stamp'];
        if ($timeStamp == 0) {
            //按日算
            $endDate = $param['start_date'].' 23:59:59';
        } else if ($timeStamp == 1) {
            //按月算
            $startDate = $startDate.'-01';
            $firstday = date('Y-m-01', strtotime($startDate));
            $endDate = date('Y-m-d', strtotime("$firstday +1 month -1 day")).' 23:59:59';
        } else {
            //自定义
            $endDate = $param['end_date'].' 23:59:59';
        }

        $type = $param['type'];
        $ids = $param['chosenVal'];
        $chosenProvinces = $param['chosenProvinces'];
        $vccId = (int) $param['vcc_id'];
        $condition = array();
        $match = array();
        $sort = array('$sort' => array('area_code' => 1));
        $group = array();
        $match['$match']['vcc_id'] = $vccId;
        if (!empty($chosenProvinces)) {
            $match['$match']['province'] = array('$in' => $chosenProvinces);
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityConditionForElasearch('ag_id', 'que_id');

        switch ($type) {
            case 'queue':
                $collection = 'rep_inbound_area_queue';
                if (!empty($authority['que_id'])){
                    $ids = array_merge($authority['que_id'], $ids);
                }
                if (!empty($ids)) {
                    $match['$match']['que_id'] = array('$in' => $ids);
                }
                $group['$group'] = array(
                    '_id' => '$province',
                    'mobile_nums' => array('$sum' => '$mobile_nums'),
                    'tel_nums' => array('$sum' => '$tel_nums'),
                    'city' => array('$first' => '$city'),
                    'area_code' => array('$last' => '$area_code')
                );
                break;
            case 'number':
                $collection = 'rep_inbound_area_trunk';
                if (!empty($ids)) {
                    $match['$match']['server_num'] = array('$in' => $ids);
                }
                $group['$group'] = array(
                    '_id' => '$province',
                    'mobile_nums' => array('$sum' => '$mobile_nums'),
                    'tel_nums' => array('$sum' => '$tel_nums'),
                    'city' => array('$first' => '$city'),
                    'area_code' => array('$last' => '$area_code')
                );
                break;
            case 'group':
                $collection = 'rep_inbound_area_group';
                if (!empty($authority['group_id'])) {
                    $ids = array_merge($authority['group_id'], $ids);
                }
                if (!empty($ids)) {
                    $match['$match']['group_id'] = array('$in' => $ids);
                }
                $group['$group'] = array(
                    '_id' => '$province',
                    'mobile_nums' => array('$sum' => '$mobile_nums'),
                    'tel_nums' => array('$sum' => '$tel_nums'),
                    'city' => array('$first' => '$city'),
                    'area_code' => array('$last' => '$area_code')
                );
                break;
            default:
                $collection = 'rep_inbound_area_queue';
                if (!empty($authority['que_id'])){
                    $ids = array_merge($authority['que_id'], $ids);
                }
                if (!empty($ids)) {
                    $match['$match']['que_id'] = array('$in' => $ids);
                }
                $group['$group'] = array(
                    '_id' => '$province',
                    'mobile_nums' => array('$sum' => '$mobile_nums'),
                    'tel_nums' => array('$sum' => '$tel_nums'),
                    'city' => array('$first' => '$city'),
                    'area_code' => array('$last' => '$area_code')
                );
                break;
        }
        $match['$match']['nowdate'] = array('$gte' => $startDate, '$lte' => $endDate);
        $condition[] = $match;
        $condition[] = $group;
        $condition[] = $sort;


        $data = $this->container->get('icsoc_core.mongodb_common.class')->getDataForCharts($condition, $collection);
        $res = array();
        foreach ($data as $k => $v) {
            $res[$k]['province_name'] = $v['_id'];
            $res[$k]['city'] = $v['city'];
            $res[$k]['area_code'] = $v['area_code'];
            $res[$k]['mobile_nums'] = $v['mobile_nums'];
            $res[$k]['tel_nums'] = $v['tel_nums'];
            $res[$k]['total'] = $v['mobile_nums'] + $v['tel_nums'];
        }

        return array('code' => 200, 'message' => 'ok', 'data' => $res);
    }

    /**
     * 按小时进线图表数据
     *
     * @param $param
     * @return array
     */
    public function getInhourChartsData($param)
    {
        if (empty($param)) {
            return array(
                'code' => 401,
                'message' => '统计参数为空'
            );
        }

        if (!is_array($param)) {
            return array(
                'code' => 402,
                'message' => '统计参数格式不为数组'
            );
        }

        if (empty($param['vcc_id'])) {
            return array(
                'code' => 403,
                'message' => '未定义的企业'
            );
        }

        if (empty($param['node'])) {
            return array(
                'code' => 405,
                'message' => '未选择统计范围'
            );
        }

        $condition = ' vcc_id = ' . $param['vcc_id'];

        /** @var  $authority (获取数据权限)*/
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 409,
                'message' => '权限内无任何数据'
            );
        } else {
            $condition .= $authority;
        }

        switch($param['type']){
            case 'queue':
                $condition .= ' AND que_id IN (' .$param['node']. ')';
                break;
            case 'number':
                $condition .= ' AND server_num IN (' .$param['node']. ')';
                break;
        }

        switch($param['time_stamp']){
            case '0':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND start_date = "' . $param['start_date'] . '"';
                }
                break;
            case '1':
                if (!empty($param['start_date'])) {
                    $startDtae = explode('-', $param['start_date']);
                    if (!is_array($startDtae) || count($startDtae) != 2) {
                        return array(
                            'code' => 405,
                            'message' => '日期格式错误'
                        );
                    }
                    $condition .= ' AND start_year = "' . $startDtae[0] . '" AND start_month = "' . $startDtae[1] . '"';
                }
                break;
            case '2':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND start_date >= "' . $param['start_date'] . '"';
                }
                if (!empty($param['end_date'])) {
                    $condition .= ' AND start_date <= "' . $param['end_date'] . '"';
                }
                break;
        }

        $result = $this->cdrDbal->fetchAll(
            "SELECT SUM(nums) AS total,start_hour " .
            "FROM rep_hour_innums WHERE " . $condition .
            " GROUP BY start_hour ORDER BY start_hour ASC"
        );

        return array(
            'code' => 200,
            'message' => 'ok',
            'data' => $result
        );
    }

    public function getInhourChartsDataFromMongo($param)
    {
        $startDate = $param['start_date'];
        $timeStamp = $param['time_stamp'];
        if ($timeStamp == 0) {
            //按日算
            $endDate = $param['start_date'].' 23:59:59';
        } else if ($timeStamp == 1) {
            //按月算
            $startDate = $startDate.'-01';
            $firstday = date('Y-m-01', strtotime($startDate));
            $endDate = date('Y-m-d', strtotime("$firstday +1 month -1 day")).' 23:59:59';
        } else {
            //自定义
            $endDate = $param['end_date'].' 23:59:59';
        }

        $type = $param['type'];
        $ids = $param['chosenIds'];
        $vccId = (int) $param['vcc_id'];
        $condition = array();
        $match = array();
        $sort = array('$sort' => array('_id' => 1));
        $group = array();
        $match['$match']['vcc_id'] = $vccId;
        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityConditionForElasearch('ag_id', 'que_id');
        switch ($type) {
            case 'queue':
                $collection = 'rep_inbound_hour_queue';
                if (!empty($authority['que_id'])) {
                    $ids = array_merge($ids, $authority['que_id']);
                }
                if (!empty($ids)) {
                    $match['$match']['que_id'] = array('$in' => $ids);
                }
                $group['$group'] = array(
                    '_id' => '$nowhour',
                    'mobile_nums' => array('$sum' => '$mobile_nums'),
                    'tel_nums' => array('$sum' => '$tel_nums')
                );
                break;
            case 'group':
                $collection = 'rep_inbound_hour_group';
                if (!empty($authority['group_id'])) {
                    $ids = array_merge($ids, $authority['group_id']);
                }
                if (!empty($ids)) {
                    $match['$match']['group_id'] = array('$in' => $ids);
                }
                $group['$group'] = array(
                    '_id' => '$nowhour',
                    'mobile_nums' => array('$sum' => '$mobile_nums'),
                    'tel_nums' => array('$sum' => '$tel_nums')
                );
                break;
            case 'number':
                $collection = 'rep_inbound_hour_trunk';
                if (!empty($ids)) {
                    $match['$match']['server_num'] = array('$in' => $ids);
                }
                $group['$group'] = array(
                    '_id' => '$nowhour',
                    'mobile_nums' => array('$sum' => '$mobile_nums'),
                    'tel_nums' => array('$sum' => '$tel_nums')
                );
                break;
            default:
                $collection = 'rep_inbound_hour_queue';
                if (!empty($authority['que_id'])) {
                    $ids = array_merge($ids, $authority['que_id']);
                }
                if (!empty($ids)) {
                    $match['$match']['que_id'] = array('$in' => $ids);
                }
                $group['$group'] = array(
                    '_id' => '$nowhour',
                    'mobile_nums' => array('$sum' => '$mobile_nums'),
                    'tel_nums' => array('$sum' => '$tel_nums')
                );
                break;
        }

        $match['$match']['nowdate'] = array('$gte' => $startDate, '$lte' => $endDate);
        $condition[] = $match;
        $condition[] = $group;
        $condition[] = $sort;

        $data = $this->container->get('icsoc_core.mongodb_common.class')->getDataForCharts($condition, $collection);
        $res = array();
        foreach ($data as $k => $v) {
            $hour = (int) $v['_id'];
            $res[$k]['start_hour'] =  $hour < 10 ? '0'.$hour : $hour;
            $res[$k]['mobile_nums'] = $v['mobile_nums'];
            $res[$k]['tel_nums'] = $v['tel_nums'];
            $res[$k]['total'] = $v['mobile_nums'] + $v['tel_nums'];
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'data' => $res
        );
    }

    /**
     * 手机固话分析图表数据
     *
     * @param $param
     * @return array
     */
    public function getCallTypeChartsData($param)
    {
        if (empty($param)) {
            return array(
                'code' => 401,
                'message' => '统计参数为空'
            );
        }

        if (!is_array($param)) {
            return array(
                'code' => 402,
                'message' => '统计参数格式不为数组'
            );
        }

        if (empty($param['vcc_id'])) {
            return array(
                'code' => 403,
                'message' => '未定义的企业'
            );
        }

        $condition = ' vcc_id = ' . $param['vcc_id'];

        switch($param['time_stamp']){
            case '0':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND start_date = "' . $param['start_date'] . '"';
                }
                break;
            case '1':
                if (!empty($param['start_date'])) {
                    $startDtae = explode('-', $param['start_date']);
                    if (!is_array($startDtae) || count($startDtae) != 2) {
                        return array(
                            'code' => 405,
                            'message' => '日期格式错误'
                        );
                    }
                    $condition .= ' AND start_year = "' . $startDtae[0] . '" AND start_month = "' . $startDtae[1] . '"';
                }
                break;
            case '2':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND start_date >= "' . $param['start_date'] . '"';
                }
                if (!empty($param['end_date'])) {
                    $condition .= ' AND start_date <= "' . $param['end_date'] . '"';
                }
                break;
        }

        $result = $this->cdrDbal->fetchAll(
            "SELECT caller_type,SUM(nums) AS total " .
            "FROM rep_caller_types " .
            "WHERE " . $condition .
            " GROUP BY caller_type"
        );

        return array(
            'code' => 200,
            'message' => 'ok',
            'data' => $result
        );
    }

    /**
     * 满意度评价图表按坐席数据
     *
     * @param $param
     * @return array
     */
    public function getEvaluateChartsByAgentData($param)
    {
        if (empty($param)) {
            return array(
                'code' => 401,
                'message' => '统计参数为空'
            );
        }

        if (!is_array($param)) {
            return array(
                'code' => 402,
                'message' => '统计参数格式不为数组'
            );
        }

        if (empty($param['vcc_id'])) {
            return array(
                'code' => 403,
                'message' => '未定义的企业'
            );
        }

        $condition = ' vcc_id = ' . $param['vcc_id'] . ' AND evaluate >= 0';

        if (!empty($param['agents'])) {
            $condition .= ' AND ag_id IN ('.$param['agents'].')';
        }

        if (!empty($param['ques'])) {
            $condition .= ' AND que_id IN ('.$param['ques'].')';
        }

        switch($param['time_stamp']){
            case '0':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND start_date = "' . $param['start_date'] . '"';
                }
                break;
            case '1':
                if (!empty($param['start_date'])) {
                    $startDtae = explode('-', $param['start_date']);
                    if (!is_array($startDtae) || count($startDtae) != 2) {
                        return array(
                            'code' => 405,
                            'message' => '日期格式错误'
                        );
                    }
                    $condition .= ' AND start_year = "' . $startDtae[0] . '" AND start_month = "' . $startDtae[1] . '"';
                }
                break;
            case '2':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND start_date >= "' . $param['start_date'] . '"';
                }
                if (!empty($param['end_date'])) {
                    $condition .= ' AND start_date <= "' . $param['end_date'] . '"';
                }
                break;
        }

        $result = $this->cdrDbal->fetchAll(
            "SELECT ag_id,ag_num,ag_name," .
            "SUM(CASE WHEN evaluate = 0 THEN 1 ELSE 0 END) AS e0," .
            "SUM(CASE WHEN evaluate = 1 THEN 1 ELSE 0 END) AS e1," .
            "SUM(CASE WHEN evaluate = 2 THEN 1 ELSE 0 END) AS e2," .
            "SUM(CASE WHEN evaluate = 3 THEN 1 ELSE 0 END) AS e3," .
            "SUM(CASE WHEN evaluate = 4 THEN 1 ELSE 0 END) AS e4," .
            "SUM(CASE WHEN evaluate = 5 THEN 1 ELSE 0 END) AS e5," .
            "SUM(CASE WHEN evaluate = 6 THEN 1 ELSE 0 END) AS e6," .
            "SUM(CASE WHEN evaluate = 7 THEN 1 ELSE 0 END) AS e7," .
            "SUM(CASE WHEN evaluate = 8 THEN 1 ELSE 0 END) AS e8," .
            "SUM(CASE WHEN evaluate = 9 THEN 1 ELSE 0 END) AS e9 " .
            "FROM win_agcdr ".
            "WHERE " . $condition .
            "GROUP BY ag_id"
        );

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => count($result),
            'data' => $result
        );
    }

    /**
     * 满意度评价图表按日期数据
     *
     * @param $param
     * @return array
     */
    public function getEvaluateChartsByDateData($param)
    {
        if (empty($param)) {
            return array(
                'code' => 401,
                'message' => '统计参数为空'
            );
        }

        if (!is_array($param)) {
            return array(
                'code' => 402,
                'message' => '统计参数格式不为数组'
            );
        }

        if (empty($param['vcc_id'])) {
            return array(
                'code' => 403,
                'message' => '未定义的企业'
            );
        }

        $condition = ' vcc_id = ' . $param['vcc_id'] . ' AND evaluate >= 0';

        if (!empty($param['agents'])) {
            $condition .= ' AND ag_id IN ('.$param['agents'].')';
        }

        if (!empty($param['ques'])) {
            $condition .= ' AND que_id IN ('.$param['ques'].')';
        }

        switch($param['time_stamp']){
            case '0':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND start_date = "' . $param['start_date'] . '"';
                }
                break;
            case '1':
                if (!empty($param['start_date'])) {
                    $startDtae = explode('-', $param['start_date']);
                    if (!is_array($startDtae) || count($startDtae) != 2) {
                        return array(
                            'code' => 405,
                            'message' => '日期格式错误'
                        );
                    }
                    $condition .= ' AND start_year = "' . $startDtae[0] . '" AND start_month = "' . $startDtae[1] . '"';
                }
                break;
            case '2':
                if (!empty($param['start_date'])) {
                    $condition .= ' AND start_date >= "' . $param['start_date'] . '"';
                }
                if (!empty($param['end_date'])) {
                    $condition .= ' AND start_date <= "' . $param['end_date'] . '"';
                }
                break;
        }

        $result = $this->cdrDbal->fetchAll(
            "SELECT start_date," .
            "SUM(CASE WHEN evaluate = 0 THEN 1 ELSE 0 END) AS e0," .
            "SUM(CASE WHEN evaluate = 1 THEN 1 ELSE 0 END) AS e1," .
            "SUM(CASE WHEN evaluate = 2 THEN 1 ELSE 0 END) AS e2," .
            "SUM(CASE WHEN evaluate = 3 THEN 1 ELSE 0 END) AS e3," .
            "SUM(CASE WHEN evaluate = 4 THEN 1 ELSE 0 END) AS e4," .
            "SUM(CASE WHEN evaluate = 5 THEN 1 ELSE 0 END) AS e5," .
            "SUM(CASE WHEN evaluate = 6 THEN 1 ELSE 0 END) AS e6," .
            "SUM(CASE WHEN evaluate = 7 THEN 1 ELSE 0 END) AS e7," .
            "SUM(CASE WHEN evaluate = 8 THEN 1 ELSE 0 END) AS e8," .
            "SUM(CASE WHEN evaluate = 9 THEN 1 ELSE 0 END) AS e9 " .
            "FROM win_agcdr ".
            "WHERE " . $condition .
            "GROUP BY start_date"
        );

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => count($result),
            'data' => $result
        );
    }

    /**
     * 坐席Tree
     *
     * @param $param
     * @return mixed
     */
    public function getAgentTree($param)
    {
        $result[0]['id'] = '0';
        $result[0]['pId'] = '-1';
        $result[0]['qid'] = '0';
        $result[0]['name'] = '全部';
        $result[0]['checked'] = true;
        $result[0]['open'] = true;

        /** @var  $authority (获取数据权限)*/
        $authority = $this->container->get("icsoc_core.common.class")->getUserTypeCondition();
       /* if (empty($param['agent']) && empty($authority['que_id'])) {
            $result[0]['name'] = '没有权限';
            $result[0]['checked'] = false;
            return $result;
        }*/

        /** @var  $queues (获取所有技能组) */
        $queues = $this->container->get('icsoc_data.model.queue')->getQueueNameKeyedByIdArray($param['vcc_id'], true);

        $i = 1;

        foreach ($queues as $key => $v) {
            if (empty($param['agent']) && isset($authority['que_id']) && !in_array($key, $authority['que_id'])) {
                continue;
            }
            if (!empty($param['agent']) && isset($authority['get_que_ids']) && !in_array($key, $authority['get_que_ids'])) {
                continue;
            }
            $j = $i;
            $result[$i]['id'] = $i;
            $result[$i]['pId'] = 0;
            $result[$i]['qid'] = $key;
            $result[$i]['name'] = $v;
            $result[$i]['open'] = false;
            $result[$i]['checked'] = true;
            $result[$i]['type'] = 'queue';
            if (isset($param['agent']) && $param['agent'] == true) {
                $result[$i]['isParent'] = true;
            } else {
                $result[$i]['isParent'] = false;
            }

            $i++;

            if (isset($param['agent']) && $param['agent'] == true) {
                /** @var  $agents (获取技能组内坐席) */
                $agents = $this->container->get('icsoc_data.model.agent')->listAgent(
                    array(
                        'vcc_code'=>$param['vcc_code'],
                        'que_id' => $key
                    )
                );

                foreach ($agents['data'] as $val) {

                    if (isset($authority['ag_id']) && !in_array($val['ag_id'], $authority['ag_id'])) {
                        continue;
                    }
                    $result[$i]['id'] = $i;
                    $result[$i]['pId'] = $j;
                    $result[$i]['qid'] = $val['ag_id'];
                    $result[$i]['name'] = $val['ag_num'] . ' ' . $val['ag_name'];
                    $result[$i]['open'] = false;
                    $result[$i]['checked'] = true;
                    $result[$i]['type'] = 'agent';
                    $i++;
                }

                if ($j == $i-1) {
                    $i--;
                    unset($result[$j]);
                }
            }

        }

        return $result;
    }

    /**
     * 按业务组
     *
     * @param $param
     * @return mixed
     */
    public function getGroupTree($param)
    {
        $result[0]['id'] = '0';
        $result[0]['pId'] = '-1';
        $result[0]['qid'] = '0';
        $result[0]['name'] = '全部';
        $result[0]['checked'] = true;
        $result[0]['open'] = true;

        /** @var  $authority (获取数据权限)*/
        $authority = $this->container->get("icsoc_core.common.class")->getUserTypeCondition();

        $agId = isset($authority['ag_id']) ? array_filter($authority['ag_id']) : array();

        if (isset($authority['ag_id']) && empty($agId)) {
            $result[0]['name'] = '没有权限';
            $result[0]['checked'] = false;
            return $result;
        }

        /** @var  $groups (获取所有业务组) */
        $em = $this->container->get('doctrine.orm.default_entity_manager');
        $groups = $em->getRepository("IcsocSecurityBundle:WinGroup")->getGroupsName($param['vcc_id']);

        $i = 1;

        foreach ($groups as $k => $v) {
            if (isset($authority['group_id']) && !in_array($k, $authority['group_id'])) {
                continue;
            }
            $j = $i;
            $result[$i]['id'] = $i;
            $result[$i]['pId'] = 0;
            $result[$i]['qid'] = $k;
            $result[$i]['name'] = $v;
            $result[$i]['open'] = false;
            $result[$i]['checked'] = true;
            $result[$i]['type'] = 'group_p';

            $i++;

            /** @var  $agents (获取业务组内坐席) */
            $agents = $this->container->get('icsoc_data.model.agent')->listAgent(
                array(
                    'vcc_code'=>$param['vcc_code'],
                    'group_id' => $k,
                )
            );

            foreach ($agents['data'] as $val) {

                if (isset($authority['ag_id']) && !in_array($val['ag_id'], $authority['ag_id'])) {
                    continue;
                }
                $result[$i]['id'] = $i;
                $result[$i]['pId'] = $j;
                $result[$i]['qid'] = $val['ag_id'];
                $result[$i]['name'] = $val['ag_num'] . ' ' . $val['ag_name'];
                $result[$i]['open'] = false;
                $result[$i]['checked'] = true;
                $result[$i]['type'] = 'group';
                $i++;
            }

            if ($j == $i-1) {
                $i--;
                unset($result[$j]);
            }
        }

        return $result;
    }

    /**
     * 号码Tree
     *
     * @param $param
     * @return mixed
     */
    public function getNumberTree($param)
    {
        $result[0]['id'] = '0';
        $result[0]['pId'] = '-1';
        $result[0]['qid'] = '0';
        $result[0]['name'] = '全部';
        $result[0]['checked'] = true;
        $result[0]['open'] = true;

        $data = $this->dbal->fetchAll(
            "SELECT phone,phone400 " .
            "FROM cc_phone400s " .
            "WHERE vcc_id = " . $param['vcc_id']
        );

        $i = 1;

        foreach ($data as $v) {
            $result[$i]['id'] = $i;
            $result[$i]['pId'] = 0;
            $result[$i]['qid'] = $v['phone'];
            $result[$i]['name'] = $v['phone'] . '[' . $v['phone400'] . ']';
            $result[$i]['open'] = false;
            $result[$i]['checked'] = true;
            $result[$i]['type'] = 'number';
            $i++;
        }

        return $result;
    }

    /**
     * 获取图形报表的选择下拉框里的选项
     *
     * @param $param
     * @return string
     */
    public function getQueuesForChosenSelect($param)
    {
        $str = '';
        switch ($param['type']){
            case 'queue':
                $queues = $this->container->get('icsoc_data.model.queue')->getQueueNameKeyedByIdArray($param['vccId'], true, true);
                foreach ($queues as $id => $name) {
                    $str .= '<option value="'.$id.'">'.$name.'</option>';
                }
                break;
            case 'group':
                $em = $this->container->get('doctrine.orm.default_entity_manager');
                $groups = $em->getRepository("IcsocSecurityBundle:WinGroup")->getGroupsName($param['vccId']);
                foreach ($groups as $id => $name) {
                    $str .= '<option value="'.$id.'">'.$name.'</option>';
                }
                break;
            case 'number':
                $data = $this->dbal->fetchAll(
                    "SELECT phone_id,phone,phone400 " .
                    "FROM cc_phone400s " .
                    "WHERE vcc_id = " . $param['vccId']
                );
                foreach ($data as $k => $v) {
                    $str .= '<option value="'.$v['phone'].'">'.$v['phone'].'['.$v['phone400'].']'.'</option>';
                }
                break;
        }

        return $str;
    }

    /**
     * 获取省下的市的呼入地区报表信息
     *
     * @param $param
     * @return array
     */
    public function getCityDataByProvinceName($param)
    {
        $startDate = $param['start_date'];
        $timeStamp = $param['time_stamp'];
        if ($timeStamp == 0) {
            //按日算
            $endDate = $param['start_date'].' 23:59:59';
        } else if ($timeStamp == 1) {
            //按月算
            $startDate = $startDate.'-01';
            $firstday = date('Y-m-01', strtotime($startDate));
            $endDate = date('Y-m-d', strtotime("$firstday +1 month -1 day")).' 23:59:59';
        } else {
            //自定义
            $endDate = $param['end_date'].' 23:59:59';
        }
        $type = $param['type'];
        $ids = $param['chosenVal'];
        $vccId = (int) $param['vccId'];
        $name = $param['name'];
        $condition = array();
        $match = array();
        $group = array();
        $match['$match']['vcc_id'] = $vccId;
        $match['$match']['province'] = $name;

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityConditionForElasearch('ag_id', 'que_id');
        switch ($type) {
            case 'queue':
                $collection = 'rep_inbound_area_queue';
                if (!empty($authority['que_id'])){
                    $ids = array_merge($authority['que_id'], $ids);
                }
                if (!empty($ids)) {
                    $match['$match']['que_id'] = array('$in' => $ids);
                }
                $group['$group'] = array(
                    '_id' => '$city',
                    'mobile_nums' => array('$sum' => '$mobile_nums'),
                    'tel_nums' => array('$sum' => '$tel_nums'),
                    'province' => array('$first' => '$province'),
                );
                break;
            case 'group':
                $collection = 'rep_inbound_area_group';
                if (!empty($authority['group_id'])) {
                    $ids = array_merge($authority['group_id'], $ids);
                }
                if (!empty($ids)) {
                    $match['$match']['group_id'] = array('$in' => $ids);
                }
                $group['$group'] = array(
                    '_id' => '$city',
                    'mobile_nums' => array('$sum' => '$mobile_nums'),
                    'tel_nums' => array('$sum' => '$tel_nums'),
                    'province' => array('$first' => '$province'),
                );
                break;
            case 'number':
                $collection = 'rep_inbound_area_trunk';
                if (!empty($ids)) {
                    $match['$match']['server_num'] = array('$in' => $ids);
                }
                $group['$group'] = array(
                    '_id' => '$city',
                    'mobile_nums' => array('$sum' => '$mobile_nums'),
                    'tel_nums' => array('$sum' => '$tel_nums'),
                    'province' => array('$first' => '$province'),
                );
                break;
            default:
                $collection = 'rep_inbound_area_queue';
                if (!empty($authority['que_id'])){
                    $ids = array_merge($authority['que_id'], $ids);
                }
                if (!empty($ids)) {
                    $match['$match']['que_id'] = array('$in' => $ids);
                }
                $group['$group'] = array(
                    '_id' => '$city',
                    'mobile_nums' => array('$sum' => '$mobile_nums'),
                    'tel_nums' => array('$sum' => '$tel_nums'),
                    'province' => array('$first' => '$province'),
                );
                break;
        }
        $match['$match']['nowdate'] = array('$gte' => $startDate, '$lte' => $endDate);
        $condition[] = $match;
        $condition[] = $group;

        $data = $this->container->get('icsoc_core.mongodb_common.class')->getDataForCharts($condition, $collection);
        $res = array();
        foreach ($data as $k => $v) {
            $res[$k]['city'] = $v['_id'];
            $res[$k]['mobile_nums'] = $v['mobile_nums'];
            $res[$k]['tel_nums'] = $v['tel_nums'];
            $res[$k]['total'] = $v['mobile_nums'] + $v['tel_nums'];
            $res[$k]['province'] = $v['province'];
        }

        return array('data' => $res);
    }
}
